/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import DTO.Object.MngOrder.MngOrderType;
import DTO.Object.MngOrder.MngOrders;
import DTO.ProductType;
import DTO.Products;
import Model.DBUtil;
import Model.OrderModel;
import Model.ProductModel;
import Model.SupportUtil;
import Model.XMLUtils;
import Model.marshallXML;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.xpath.XPathExpressionException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 *
 * @author TuPA
 */
public class AdminController extends HttpServlet {

    private Products productList = new Products();

    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        PrintWriter out = response.getWriter();
        String realpath = getServletContext().getRealPath("/");
        HttpSession session = request.getSession(true);
        String action = (String) request.getParameter("btAction");
        try {
            if (action.equals("editCheckbox")) {
                boolean checkUpdate = false;
                int positionProduct = 0;
                String idProduct = request.getParameter("txtID");
                String proType = request.getParameter("txtProType");
                String noSell = request.getParameter("txtSell");
                String filePath = realpath + "XML/products.xml";

                boolean validateXMLProduct = ProductModel.XMLvalidateSchema(filePath, realpath + "schema/products.xsd");
                if (validateXMLProduct) {
                    Products products = (Products) SupportUtil.unmarshal("DTO", realpath + "XML/products.xml");
                    for (int i = 0; i < products.getProduct().size(); i++) {
                        if (products.getProduct().get(i).getProID() == Integer.parseInt(idProduct)) {
                            products.getProduct().get(i).setProType(Integer.parseInt(proType));
                            products.getProduct().get(i).setNoSell(Integer.parseInt(noSell));
                            positionProduct = i;
                        }
                    }

                    SupportUtil.marshal(realpath + "XML/products.xml", products);
                    checkUpdate = ProductModel.updateProductType(products, idProduct, positionProduct);
                    response.setContentType("text/plain");
                    if (checkUpdate == true) {
                        out.println("Update Sucessfully!");
                    } else {
                        out.println("Update Unsucessfully!");
                    }
                } else {
                    out.println("Update Unsucessfully!");
                }
            } else if (action.equals("updateStatus")) {
//                int positionOrder = 0;
                boolean checkUpdate = false;
                String orderID = request.getParameter("txtOrderID");
                String status = request.getParameter("txtStatus");

                String filePath = realpath + "XML/listOrder.xml";
                Document doc = XMLUtils.buildDOMTree(filePath);
                OrderModel.searchAndModify(doc, Integer.valueOf(orderID), status);
                XMLUtils.writeXML(doc, filePath);

                boolean validateXMLOrder = ProductModel.XMLvalidateSchema(filePath, realpath + "schema/MngOrder.xsd");
                if (validateXMLOrder) {
                    MngOrders mngOrder = (MngOrders) SupportUtil.unmarshal("DTO.Object.MngOrder", realpath + "XML/listOrder.xml");
                    checkUpdate = OrderModel.updateStatusOrder(mngOrder, orderID);
                    if (checkUpdate == true) {
                        marshallXML mXML = new marshallXML();
                        mXML.marshalProduct(realpath + "XML/products.xml");
                        SupportUtil.marshal(realpath + "XML/listOrder.xml", mngOrder);
                        out.println("Successfully!");
                    } else {
                        out.println("Unsuccessfully!");
                    }
                } else {
                    out.println("Unsuccessfully!");
                }
            } else if (action.equals("searchProduct")) {
                String name = request.getParameter("txtProductName");

                String filePath = realpath + "XML/products.xml";
                Document doc = XMLUtils.buildDOMTree(filePath);

                productList = new Products();
                searchLikeName(doc, name);
                SupportUtil.marshal(realpath + "XML/resultProductSearch.xml", productList);
            } else if (action.equals("checkProductName")) {
                String productName = request.getParameter("txtname");

                DBUtil util = new DBUtil();
                Connection conn = null;
                Statement stm = null;
                String query = null;

                try {
                    Class.forName(util.getDriverName());
                    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
                    conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
                    query = "Select proName from Product";
                    stm = conn.createStatement();
                    ResultSet name = stm.executeQuery(query);
                    while (name.next()) {
                        if (productName.equals(name.getString("proName"))) {
                            String txt = "This name is duplicated. Please input other name!";
                            response.setContentType("text/plain");
                            out.println(txt);
                            break;
                        }
                    }
                } catch (Exception e) {
                }
            } else if (action.equals("showOrderDetail")) {
                String id = request.getParameter("txtOrderID");
                String appPath = getServletContext().getRealPath("/");
                String filePath = appPath + "XML/showOrderDetail.xml";
                XMLUtils.removeOrderXML(filePath);

                Document doc = XMLUtils.buildDOMTree(filePath);

                DBUtil util = new DBUtil();
                Connection conn = null;
                Statement stm = null;
                String query = null;
                int result = 0;
                try {
                    Class.forName(util.getDriverName());
                    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
                    conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
                    query = "Select tblOrder.userName, Product.Image, Product.proName, Product.Price, tblOrder.quantity, tblOrder.priceProduct, tblOrder.orderDate"
                            + " From Product inner join"
                            + " (Select OrderDetail.proID, OrderDetail.quantity, OrderDetail.priceProduct, OrderPayment.userName, OrderPayment.orderDate"
                            + " From OrderDetail inner join OrderPayment on OrderDetail.orderMother=OrderPayment.orderID"
                            + " where OrderDetail.orderMother='"+ id +"') as tblOrder on Product.proID=tblOrder.proID";
                    stm = conn.createStatement();
                    ResultSet userIDList = stm.executeQuery(query);
                    while (userIDList.next()) {
                        Element orderDetail = doc.createElement("OrderDetail");

                        Element userName = doc.createElement("userName");
                        userName.setTextContent(userIDList.getString(1));
                        orderDetail.appendChild(userName);

                        Element proImage = doc.createElement("proImage");
                        proImage.setTextContent(userIDList.getString(2));
                        orderDetail.appendChild(proImage);

                        Element proName = doc.createElement("proName");
                        proName.setTextContent(userIDList.getString(3));
                        orderDetail.appendChild(proName);

                        Element Price = doc.createElement("Price");
                        Price.setTextContent(userIDList.getString(4));
                        orderDetail.appendChild(Price);

                        Element Quantity = doc.createElement("Quantity");
                        Quantity.setTextContent(userIDList.getString(5));
                        orderDetail.appendChild(Quantity);

                        Element totalPrice = doc.createElement("totalPrice");
                        totalPrice.setTextContent(userIDList.getString(6));
                        orderDetail.appendChild(totalPrice);

                        Element Date = doc.createElement("orderDate");
                        Date.setTextContent(userIDList.getString(7));
                        orderDetail.appendChild(Date);

                        Node root = doc.getFirstChild();
                        root.appendChild(orderDetail);
                        result++;

                    }
                    XMLUtils.writeXML(doc, filePath);
                    out.println(result);
                } catch (Exception e) {
                }
            } else if (action.equals("listOrder")) {
                String month = request.getParameter("txtMonth");
                String year = request.getParameter("txtYear");
                String appPath = getServletContext().getRealPath("/");
                String filePath = appPath + "XML/listOrder.xml";
                DBUtil util = new DBUtil();
                Connection conn = null;
                Statement stm = null;
                String query = null;
                SupportUtil SUtil = new SupportUtil();
                int result = 0;
                ResultSet orderList = null;
                try {
                    MngOrders mngOrder = new MngOrders();
                    Class.forName(util.getDriverName());
                    DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
                    conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
                    if (Integer.valueOf(month) == 0) {
                        query = "select final.orderMother as orderID, UserAccount.UserName, UserAccount.FullName, final.orderDate, final.totalQuantity, final.totalPrice, final.orderStatus"
                                + " from UserAccount inner join (select queryTotalQuantity.orderMother, queryTotalQuantity.userName, queryTotalQuantity.orderDate,"
                                + " queryTotalQuantity.orderStatus, queryTotalQuantity.totalQuantity, queryTotalPrice.totalPrice"
                                + " from (select OrderDetail.orderMother, OrderPayment.userName, OrderPayment.orderDate, OrderPayment.orderStatus, SUM(OrderDetail.quantity) as totalQuantity"
                                + " from OrderPayment inner join OrderDetail on OrderPayment.orderID=OrderDetail.orderMother"
                                + " group by OrderDetail.orderMother, OrderPayment.userName,OrderPayment.orderDate, OrderPayment.orderStatus) as queryTotalQuantity"
                                + " inner join (select queryPrice.orderMother, Sum(queryPrice.totalPrice) as totalPrice"
                                + " from (select OrderDetail.orderMother,OrderDetail.priceProduct*OrderDetail.quantity as totalPrice from OrderDetail inner join OrderPayment"
                                + " on OrderDetail.orderMother=OrderPayment.orderID"
                                + " group by OrderDetail.orderMother, OrderDetail.priceProduct,OrderDetail.quantity) as queryPrice"
                                + " group by queryPrice.orderMother) as queryTotalPrice"
                                + " on queryTotalQuantity.orderMother=queryTotalPrice.orderMother) as final on UserAccount.UserName=final.userName"
                                + " where YEAR(final.orderDate)=" + Integer.valueOf(year);
                    } else {
                        query = "select final.orderMother as orderID, UserAccount.UserName, UserAccount.FullName, final.orderDate, final.totalQuantity, final.totalPrice, final.orderStatus"
                                + " from UserAccount inner join (select queryTotalQuantity.orderMother, queryTotalQuantity.userName, queryTotalQuantity.orderDate,"
                                + " queryTotalQuantity.orderStatus, queryTotalQuantity.totalQuantity, queryTotalPrice.totalPrice"
                                + " from (select OrderDetail.orderMother, OrderPayment.userName, OrderPayment.orderDate, OrderPayment.orderStatus, SUM(OrderDetail.quantity) as totalQuantity"
                                + " from OrderPayment inner join OrderDetail on OrderPayment.orderID=OrderDetail.orderMother"
                                + " group by OrderDetail.orderMother, OrderPayment.userName,OrderPayment.orderDate, OrderPayment.orderStatus) as queryTotalQuantity"
                                + " inner join (select queryPrice.orderMother, Sum(queryPrice.totalPrice) as totalPrice"
                                + " from (select OrderDetail.orderMother,OrderDetail.priceProduct*OrderDetail.quantity as totalPrice from OrderDetail inner join OrderPayment"
                                + " on OrderDetail.orderMother=OrderPayment.orderID"
                                + " group by OrderDetail.orderMother, OrderDetail.priceProduct,OrderDetail.quantity) as queryPrice"
                                + " group by queryPrice.orderMother) as queryTotalPrice"
                                + " on queryTotalQuantity.orderMother=queryTotalPrice.orderMother) as final on UserAccount.UserName=final.userName"
                                + " where YEAR(final.orderDate)=" + Integer.valueOf(year)
                                + " and MONTH(final.orderDate)=" + Integer.valueOf(month);
                    }
                    stm = conn.createStatement();
                    orderList = stm.executeQuery(query);
                    while (orderList.next()) {
                        MngOrderType mngOrderType = new MngOrderType();
                        mngOrderType.setOrderID(Integer.valueOf(orderList.getString(1)));
                        mngOrderType.setUsername(orderList.getString(2));
                        mngOrderType.setFullname(orderList.getString(3));
                        mngOrderType.setOrderDate(orderList.getString(4));
                        mngOrderType.setTotalQuantity(Integer.valueOf(orderList.getString(5)));
                        mngOrderType.setTotalPrice(Integer.valueOf(orderList.getString(6)));
                        mngOrderType.setOrderStatus(Integer.valueOf(orderList.getString(7)));
                        mngOrder.getMngOrder().add(mngOrderType);
                        result++;

                    }
                    SUtil.marshal(filePath, mngOrder);
                    out.println(result);
                } catch (Exception e) {
                }
            }
        } catch (XPathExpressionException ex) {
            Logger.getLogger(AdminController.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            out.close();
        }
    }

    private void searchLikeName(Node node, String name) {
        if (node == null) {
            return;
        }
        String nodeName = node.getNodeName();
        if (nodeName.equals("product")) {
            //tim node con cua no
            NodeList proChildren = node.getChildNodes();
            String proName = "", Description = "", brandName = "", catalogName = "", proFrom = "", Image = "";
            int proID = 0, SellPrice = 0, proType = 0, proNumber = 0, noSell = 0;
            for (int i = 0; i < proChildren.getLength(); i++) {
                Node tmp = proChildren.item(i);
                String tmpName = tmp.getNodeName();
                if (tmpName.equals("proID")) {
                    proID = Integer.parseInt(tmp.getTextContent().trim());
                } else if (tmp.getNodeName().equals("proName")) {
                    proName = tmp.getTextContent().trim();
                    if (proName.toLowerCase().indexOf(name.toLowerCase()) == -1) {
                        break;
                    }
                } else if (tmp.getNodeName().equals("Price")) {
                    SellPrice = Integer.parseInt(tmp.getTextContent().trim());
                } else if (tmp.getNodeName().equals("Description")) {
                    Description = tmp.getTextContent().trim();
                } else if (tmp.getNodeName().equals("Image")) {
                    Image = tmp.getTextContent().trim();
                } else if (tmp.getNodeName().equals("catalogName")) {
                    catalogName = tmp.getTextContent().trim();
                } else if (tmp.getNodeName().equals("proType")) {
                    proType = Integer.parseInt(tmp.getTextContent().trim());
                } else if (tmp.getNodeName().equals("proNumber")) {
                    proNumber = Integer.parseInt(tmp.getTextContent().trim());
                } else if (tmp.getNodeName().equals("proFrom")) {
                    proFrom = tmp.getTextContent().trim();
                } else if (tmp.getNodeName().equals("brandName")) {
                    brandName = tmp.getTextContent().trim();
                } else if (tmp.getNodeName().equals("noSell")) {
                    noSell = Integer.parseInt(tmp.getTextContent().trim());
                    ProductType pro = new ProductType(proID, proName, SellPrice, Description, Image, catalogName, proType, proNumber, proFrom, brandName, noSell);
                    productList.getProduct().add(pro);
                }
            }

        }
        NodeList children = node.getChildNodes();
        int i = 0;
        while (i < children.getLength()) {
            searchLikeName(children.item(i++), name);
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
